package com.pks.autoInsurance.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.pks.autoInsurance.dto.PersonalDetailsDto;
import com.pks.autoInsurance.dto.VehicleDetailsDto;

public class AdminDAOImpl extends AutoInsuranceDAO implements AdminDAO {
	/*
	 * This method is used to retrive the list of all the users from the
	 * database.
	 * 
	 * @see com.cts.autoInsurance.dao.AdminDAO#getUsersList()
	 */
	@Override
	public List<PersonalDetailsDto> getUsersList() {
		System.out.println(getClass() + "::::getUsersList::::begin");
		Connection connection = getConnection();
		List<PersonalDetailsDto> personList = new ArrayList<PersonalDetailsDto>();
		PersonalDetailsDto personalDetailsDto;
		String sql = "SELECT USER_ID,FIRST_NAME,LAST_NAME,MOBILE_NO,CITY,EMAIL_ID," +
				"ADDRESS,DATE_OF_BIRTH,GENDER FROM REGISTRATION";
		try {
			connection.setAutoCommit(false);
			PreparedStatement preparedStatement = connection
					.prepareStatement(sql);
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				personalDetailsDto = new PersonalDetailsDto();
				personalDetailsDto.setUserId(resultSet.getString("USER_ID"));
				personalDetailsDto.setFirstName(resultSet
						.getString("FIRST_NAME"));
				personalDetailsDto
						.setLastName(resultSet.getString("LAST_NAME"));
				personalDetailsDto
						.setMobileNo(resultSet.getString("MOBILE_NO"));
				personalDetailsDto.setCity(resultSet.getString("CITY"));
				personalDetailsDto.setEmailId(resultSet.getString("EMAIL_ID"));
				personalDetailsDto.setAddress(resultSet.getString("ADDRESS"));
				personalDetailsDto.setDateOfBirth(resultSet
						.getDate("DATE_OF_BIRTH"));
				personalDetailsDto.setGender(resultSet.getString("GENDER"));
				personList.add(personalDetailsDto);
			}

			return personList;

		} catch (SQLException e) {
			System.err.println(getClass() + ":::getUsersList() SQLException"
					+ e.getMessage());
			return null;
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				System.err.println(getClass() + "::::" + e.getMessage());
			}
			System.out.println(getClass() + "::::getUsersList::::end");
		}
	}

	/*
	 * This method is used for searching specific user in the database.
	 * 
	 * @see com.cts.autoInsurance.dao.AdminDAO#getUsersList(java.lang.String)
	 */
	@Override
	public List<PersonalDetailsDto> getUsersList(String userId) {
		System.out.println(getClass() + "::::getUsersList::::begin");
		Connection connection = getConnection();
		List<PersonalDetailsDto> userPersonalDetailsList = new ArrayList<PersonalDetailsDto>();
		PersonalDetailsDto personalDetailsDto;
		String sql = "SELECT USER_ID,FIRST_NAME,LAST_NAME,MOBILE_NO,CITY,EMAIL_ID,ADDRESS,DATE_OF_BIRTH,GENDER FROM REGISTRATION WHERE USER_ID LIKE ?";
		try {
			connection.setAutoCommit(false);
			PreparedStatement preparedStatement = connection
					.prepareStatement(sql);
			preparedStatement.setString(1, "%" + userId + "%");
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				personalDetailsDto = new PersonalDetailsDto();
				personalDetailsDto.setUserId(resultSet.getString("USER_ID"));
				personalDetailsDto.setFirstName(resultSet
						.getString("FIRST_NAME"));
				personalDetailsDto
						.setLastName(resultSet.getString("LAST_NAME"));
				personalDetailsDto
						.setMobileNo(resultSet.getString("MOBILE_NO"));
				personalDetailsDto.setCity(resultSet.getString("CITY"));
				personalDetailsDto.setEmailId(resultSet.getString("EMAIL_ID"));
				personalDetailsDto.setAddress(resultSet.getString("ADDRESS"));
				personalDetailsDto.setDateOfBirth(resultSet
						.getDate("DATE_OF_BIRTH"));
				personalDetailsDto.setGender(resultSet.getString("GENDER"));
				userPersonalDetailsList.add(personalDetailsDto);
			}
			System.out.println("List retrived from system::::"
					+ userPersonalDetailsList);
			return userPersonalDetailsList;

		} catch (SQLException e) {
			System.err.println(getClass() + ":::getUsersList() SQLException"
					+ e.getMessage());
			return null;
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				System.err.println(getClass() + "::::" + e.getMessage());
			}
			System.out.println(getClass() + "::::getUsersList::::end");
		}
	}

	/*
	 * This method is used for removing user information from the database.
	 * 
	 * @see com.cts.autoInsurance.dao.AdminDAO#deleteUser(java.lang.String)
	 */
	@Override
	public int deleteUser(String userId) {
		System.out.println(getClass() + "::::deleteUser::::begin");
		Connection connection = getConnection();
		String sqlRegistrationDelete = "DELETE FROM REGISTRATION WHERE USER_ID=?";
		String sqlUserLoginDelete = "DELETE FROM USERS_LOGIN WHERE USER_ID=?";
		try {
			connection.setAutoCommit(false);
			PreparedStatement preparedStatement = connection
					.prepareStatement(sqlRegistrationDelete);
			preparedStatement.setString(1, userId);
			preparedStatement.executeUpdate();
			preparedStatement = connection.prepareStatement(sqlUserLoginDelete);
			preparedStatement.setString(1, userId);
			return preparedStatement.executeUpdate();
		} catch (SQLException e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				System.err.println(getClass() + "::::" + e1.getMessage());
				e.printStackTrace();
			}
			System.err.println(getClass() + "::::" + e.getMessage());
		} finally {
			try {
				connection.commit();
				connection.close();
			} catch (SQLException e) {
				System.err.println(getClass() + "::::" + e.getMessage());
			}
			System.out.println(getClass() + "::::deleteUser::::end");
		}
		return 0;
	}

	/*
	 * This method is used for retriving user specific vehicle details
	 * 
	 * @see
	 * com.cts.autoInsurance.dao.AdminDAO#getUserVehicleDetail(java.lang.String)
	 */
	@Override
	public VehicleDetailsDto getUserVehicleDetail(String userId) {
		System.out.println(getClass() + "::::getUserVehicleDetail::::begin");
		Connection connection = getConnection();
		String sql = "SELECT TYPE,MODEL,MAKE,MF_YEAR,VEHICLE_REGISTRATION_NO,POLICY_TYPE,POLICY_AMOUNT,QUOTE_DATE FROM VEHICLE_DETAILS WHERE USER_ID=?";
		try {
			connection.setAutoCommit(false);
			PreparedStatement preparedStatement = connection
					.prepareStatement(sql);
			preparedStatement.setString(1, userId);
			ResultSet resultSet = preparedStatement.executeQuery();
			VehicleDetailsDto vehicleDetailsDto = new VehicleDetailsDto();
			if (resultSet.next()) {
				vehicleDetailsDto.setUserId(userId);
				vehicleDetailsDto.setType(resultSet.getString("TYPE"));
				vehicleDetailsDto.setModel(resultSet.getString("MODEL"));
				vehicleDetailsDto.setMake(resultSet.getString("MAKE"));
				vehicleDetailsDto.setMfYear(resultSet.getString("MF_YEAR"));
				vehicleDetailsDto.setVehicleRegistrationNo(resultSet
						.getString("VEHICLE_REGISTRATION_NO"));
				vehicleDetailsDto.setPolicyType(resultSet
						.getString("POLICY_TYPE"));
				vehicleDetailsDto.setPolicyAmount(resultSet
						.getInt("POLICY_AMOUNT"));
				vehicleDetailsDto.setQuoteDate(resultSet.getDate("QUOTE_DATE"));
			}
			System.out.println(getClass() + "::::getUserVehicleDetail::::end");
			return vehicleDetailsDto;
		} catch (SQLException e) {
			System.err.println(getClass() + "::::" + e.getMessage());
			return null;
		} finally {
			try {
				connection.close();
			} catch (SQLException e) {
				System.err.println(getClass() + "::::" + e.getMessage());
			}
		}
	}

	@Override
	/*
	 * This method is used for updating User Details.
	 */
	public int updateUser(VehicleDetailsDto vehicleDetailsDtoReq) {
		System.out.println(getClass() + "::::updateUser::::begin");
		System.out.println(getClass() + "::::vehicleDetailsDtoReq:"
				+ vehicleDetailsDtoReq);
		Connection connection = getConnection();
		try {
			connection.setAutoCommit(false);
			String sql = "UPDATE VEHICLE_DETAILS SET VEHICLE_REGISTRATION_NO=?,POLICY_AMOUNT=? WHERE USER_ID=?";
			PreparedStatement preparedStatement = connection
					.prepareStatement(sql);
			preparedStatement.setString(1,
					vehicleDetailsDtoReq.getVehicleRegistrationNo());
			preparedStatement.setInt(2, vehicleDetailsDtoReq.getPolicyAmount());
			preparedStatement.setString(3, vehicleDetailsDtoReq.getUserId());
			int updatedRows = preparedStatement.executeUpdate();
			System.out.println(getClass() + "::::updatedRows=" + updatedRows);
			connection.commit();
			return updatedRows;
		} catch (SQLException e) {
			System.err.println(getClass() + "::::" + e.getMessage());
			return 0;
		} finally {
			try {
				connection.rollback();
				connection.close();
			} catch (SQLException e) {
				System.err.println(getClass() + "::::" + e.getMessage());
			}
			System.out.println(getClass() + "::::updateUser::::begin");
		}
	}
}
